package noobteam.deartrainer.mapper;

import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author
 */
@Mapper
public interface CourseInfoMapper {

    /**
     * 一般由教练进行
     * 创建课程
     */
    @Insert("INSERT INTO courseinfo (courseName, courseHours, courseTypeId, trainerId, coursePrice, courseMaxStudents, " +
            "courseStartTime, courseDescription, courseEndDate) " +
            "VALUES (#{course.courseName}, #{course.courseHours}, #{course.courseTypeId}, #{course.trainerId}, #{course.coursePrice}, " +
            "#{course.courseMaxStudents}, #{course.courseStartTime}, #{course.courseDescription}, #{course.courseEndTime})")
    public boolean createCourse(@Param("course") Map<String, String> map);

    @Select("SELECT c.courseTypeId ,c.courseType \n" +
            "FROM deartrainer.coursetypecode c ")
    public List<Map<String, Object>> findAllCourseCodeInfo();

    /**
     * 获取课程列表
     */
    @Select("SELECT courseId,courseName, courseHours, courseType, trainerName, coursePrice, " +
            "courseMaxStudents, courseStudents,courseDescription,coursePhoto, courseStartTime " +
            "FROM courseinfo, coursetypecode, trainersinfo " +
            "WHERE courseinfo.courseTypeId = coursetypecode.courseTypeId AND courseinfo.trainerId = trainersinfo.trainerId ")
    public List<Map<String, Object>> getALLCourseList();

    /**
     * 按照热度（上课人数）获取课程列表
     * 课程按照上课人数从多到少排序
     */
    @Select("SELECT * " +
            "FROM courseinfo, coursetypecode, trainersinfo " +
            "WHERE courseinfo.courseTypeId = coursetypecode.courseTypeId AND courseinfo.trainerId = trainersinfo.trainerId " +
            "ORDER BY courseStudents DESC")
    public List<Map<String, Object>> getCourseListOrderByNum();

    /**
     * 按照热度（上课人数）获取课程列表
     * 课程按照上课人数从多到少排序
     */
    @Select("SELECT * " +
            "FROM courseinfo, coursetypecode, trainersinfo " +
            "WHERE courseinfo.courseTypeId = coursetypecode.courseTypeId AND courseinfo.trainerId = trainersinfo.trainerId " +
            "ORDER BY courseName")
    public List<Map<String, Object>> getCourseListOrderByName();

    /**
     * 按照开课日期获取课程列表
     * 只会获取今天及以后的课程
     * 获取的课程按照日期由近到远排列
     */
    @Select("SELECT * " +
            "FROM courseinfo, coursetypecode, trainersinfo " +
            "WHERE courseinfo.courseTypeId = coursetypecode.courseTypeId AND courseinfo.trainerId = trainersinfo.trainerId AND courseStartTime >= #{date} " +
            "ORDER BY courseStartTime")
    public List<Map<String, Object>> getCourseListOrderByDate(@Param("date") LocalDate date);

    /**
     * 获取某一类型的课程列表
     * 输入为类型名称
     */
    @Select("SELECT courseId, courseName, courseHours, courseType, trainerName, coursePrice, courseMaxStudents, courseStudents, courseStartTime " +
            "FROM courseinfo, coursetypecode, trainersinfo " +
            "WHERE courseType = #{name} AND courseinfo.courseTypeId = coursetypecode.courseTypeId AND courseinfo.trainerId = trainersinfo.trainerId")
    public List<Map<String, Object>> getCourseOfType(@Param("name") String name);

    /**
     * 获取某门课的详细信息
     */
    @Select("SELECT * FROM courseinfo, coursetypecode, trainersinfo " +
            "WHERE courseId = #{courseId} AND courseinfo.courseTypeId = coursetypecode.courseTypeId AND courseinfo.trainerId = trainersinfo.trainerId")
    public Map<String, Object> getCourseMsg(@Param("courseId") Integer courseId);

    /**
     * 修改课程信息
     */
    @Update("UPDATE courseinfo " +
            "SET courseName = #{course.courseName}, " +
            "courseHours = #{course.courseHours}, " +
            "courseTypeId = #{course.courseTypeId}, " +
            "coursePrice = #{course.coursePrice}, " +
            "courseMaxStudents = #{course.courseMaxStudents}, " +
            "courseStartTime = #{course.courseStartTime}, " +
            "courseEndDate = #{course.courseEndTime}, " +
            "courseDescription = #{course.courseDescription} " +
            "WHERE courseId = #{course.courseId}")
    public boolean updateCourseMsg(@Param("course") Map<String, String> p);

    /**
     * 删除课程
     */
    @Delete("DELETE FROM courseinfo WHERE courseId = #{courseId}")
    public boolean deleteCourseById(@Param("courseId") int courseId);

    /**
     * 根据courseid查询课程
     * @return
     */
    @Select("SELECT * " +
            "FROM courseinfo c1, coursetypecode c2, trainersinfo t " +
            "WHERE c1.courseTypeId = c2.courseTypeId AND c1.trainerId = t.trainerId AND c1.courseId = #{courseId} ")
    Map<String, Object> getCourseInfoById(int courseId);


    /**
     * 根据 trainerPhone 查询当前教练的课程
     * @return
     */
    @Select("SELECT *\n" +
            "FROM courseinfo c ,trainersinfo t \n" +
            "WHERE c.trainerId = t.trainerId AND t.trainerPhone = #{trainerPhone}")
    List<Map<String, Object>> getCourseInfoByTrainerPhone(int trainerPhone);

   /* @Select("SELECT courseId,courseName, courseHours, courseType, trainerName, coursePrice, " +
            "courseMaxStudents, courseStudents,courseDescription,coursePhoto " +
            "FROM courseinfo, coursetypecode, trainersinfo " +
            "WHERE courseinfo.courseTypeId = coursetypecode.courseTypeId AND courseinfo.trainerId = trainersinfo.trainerId AND courseId = #{courseId} ")
    Map<String, Object> getCourseInfoById(@Param("courseId") Integer courseId);*/

    /**
     * 查询课程价格
     */
    @Select("SELECT coursePrice FROM courseinfo WHERE courseId = #{courseId}")
    public double getCoursePrice(@Param("courseId") Integer courseId);

    /**
     * 选课后人数增加
     */
    @Update("UPDATE courseinfo SET courseStudents = courseStudents + 1 WHERE courseId = #{courseId}")
    public boolean addStudent(@Param("courseId") Integer courseId);

    /**
     * 退课后人数减少
     */
    @Update("UPDATE courseinfo SET courseStudents = courseStudents - 1 WHERE courseId = #{courseId}")
    public boolean deleteStudent(@Param("courseId") Integer courseId);

    /**
     * 根据 trainerId 查询当前教练的课程
     * 按照选课人数倒序排列
     * @return
     */
    @Select("SELECT * " +
            "FROM courseinfo " +
            "WHERE trainerId = #{trainerId} " +
            "ORDER BY courseStudents DESC")
    List<Map<String, Object>> getTrainerCourseOrderByStudentNum(int trainerId);

    /**
     * 根据 trainerId 查询当前教练的课程
     * 按照开课日期排列
     * @return
     */
    @Select("SELECT * " +
            "FROM courseinfo " +
            "WHERE trainerId = #{trainerId} " +
            "ORDER BY courseStartTime")
    List<Map<String, Object>> getTrainerCourseOrderByStartTime(int trainerId);

    /**
     * 更新课程头像
     * @param map
     * @return
     */
    @Update("UPDATE courseinfo SET coursePhoto = #{js.coursePhoto} " +
            "WHERE courseId = #{js.courseId}")
    boolean updateCoursePhoto(@Param("js") Map<String, String> map);
}
